import sqlite3

# 数据库配置常量（集中管理，便于修改）
DB_PATH = 'test_database.db'

# --------------------------
# 1. 封装创建表函数（初始化必备）
# --------------------------
def create_users_table(cursor):
    """创建 users 表（若不存在）"""
    create_sql = '''
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            age INTEGER,
            email TEXT UNIQUE
        )
    '''
    try:
        cursor.execute(create_sql)
        print("✅ 表创建成功（或已存在）")
    except sqlite3.Error as e:
        raise sqlite3.Error(f"创建表失败：{e}")  # 抛出异常，由主逻辑统一处理

# --------------------------
# 2. 封装新增数据函数（支持单条/批量）
# --------------------------
def add_user(cursor, name, age, email):
    """新增单条用户数据"""
    insert_sql = "INSERT OR IGNORE INTO users (name, age, email) VALUES (?, ?, ?)"
    try:
        cursor.execute(insert_sql, (name, age, email))
        return cursor.rowcount  # 返回影响行数（0 表示重复，1 表示新增）
    except sqlite3.Error as e:
        raise sqlite3.Error(f"新增用户失败：{e}")

def batch_add_users(cursor, users_list):
    """批量新增用户数据（users_list 格式：[(name, age, email), ...]）"""
    insert_sql = "INSERT OR IGNORE INTO users (name, age, email) VALUES (?, ?, ?)"
    try:
        cursor.executemany(insert_sql, users_list)
        return cursor.rowcount  # 返回新增成功的条数
    except sqlite3.Error as e:
        raise sqlite3.Error(f"批量新增用户失败：{e}")

# --------------------------
# 3. 封装查询数据函数（支持全查/条件查）
# --------------------------
def get_all_users(cursor):
    """查询所有用户数据"""
    query_sql = "SELECT * FROM users"
    try:
        cursor.execute(query_sql)
        return cursor.fetchall()  # 返回所有结果（元组列表）
    except sqlite3.Error as e:
        raise sqlite3.Error(f"查询所有用户失败：{e}")

def get_users_by_condition(cursor, condition_sql="", params=()):
    """按条件查询用户（示例：condition_sql='WHERE age > ?', params=(28,)）"""
    query_sql = f"SELECT * FROM users {condition_sql}"
    try:
        cursor.execute(query_sql, params)
        return cursor.fetchall()
    except sqlite3.Error as e:
        raise sqlite3.Error(f"条件查询失败：{e}")

# --------------------------
# 4. 封装更新数据函数（支持按姓名更新年龄）
# --------------------------
def update_user_age(cursor, name, new_age):
    """根据姓名更新用户年龄"""
    update_sql = "UPDATE users SET age = ? WHERE name = ?"
    try:
        cursor.execute(update_sql, (new_age, name))
        return cursor.rowcount  # 返回更新成功的条数
    except sqlite3.Error as e:
        raise sqlite3.Error(f"更新用户年龄失败：{e}")

# --------------------------
# 5. 封装删除数据函数（支持按姓名/邮箱删除）
# --------------------------
def delete_user_by_name(cursor, name):
    """根据姓名删除用户"""
    delete_sql = "DELETE FROM users WHERE name = ?"
    try:
        cursor.execute(delete_sql, (name,))
        return cursor.rowcount  # 返回删除成功的条数
    except sqlite3.Error as e:
        raise sqlite3.Error(f"删除用户失败：{e}")

def delete_user_by_email(cursor, email):
    """根据邮箱删除用户（唯一键，更精准）"""
    delete_sql = "DELETE FROM users WHERE email = ?"
    try:
        cursor.execute(delete_sql, (email,))
        return cursor.rowcount
    except sqlite3.Error as e:
        raise sqlite3.Error(f"删除用户失败：{e}")

# --------------------------
# 主逻辑（调用封装函数执行操作）
# --------------------------
def main():
    # 用 with 管理连接，自动处理连接打开/关闭和事务
    with sqlite3.connect(DB_PATH) as conn:
        cursor = conn.cursor()  # 游标与当前连接绑定，避免混用

        try:
            # 1. 创建表
            create_users_table(cursor)

            # 2. 新增数据（单条 + 批量）
            single_add_count = add_user(cursor, "Alice", 25, "alice@example.com")
            print(f"\n✅ 单条新增：{'成功' if single_add_count > 0 else '已存在'}")

            batch_users = [
                ("Bob", 30, "bob@example.com"),
                ("Charlie", 28, "charlie@example.com"),
                ("David", 35, "david@example.com")
            ]
            batch_add_count = batch_add_users(cursor, batch_users)
            print(f"✅ 批量新增：成功 {batch_add_count} 条（已忽略重复数据）")

            # 3. 查询数据
            print("\n📋 所有用户：")
            all_users = get_all_users(cursor)
            for user in all_users:
                print(f"ID: {user[0]}, 姓名: {user[1]}, 年龄: {user[2]}, 邮箱: {user[3]}")

            print("\n📋 年龄大于28的用户：")
            adult_users = get_users_by_condition(cursor, "WHERE age > ?", (28,))
            for user in adult_users:
                print(f"ID: {user[0]}, 姓名: {user[1]}, 年龄: {user[2]}")

            # 4. 更新数据
            update_count = update_user_age(cursor, "Alice", 26)
            print(f"\n✅ 更新用户年龄：{'成功' if update_count > 0 else '无匹配用户'}（影响 {update_count} 条）")

            # 5. 删除数据
            delete_count = delete_user_by_name(cursor, "Bob")
            print(f"✅ 删除用户：{'成功' if delete_count > 0 else '无匹配用户'}（影响 {delete_count} 条）")

            # 6. 验证删除后的数据
            print("\n📋 删除后的所有用户：")
            updated_users = get_all_users(cursor)
            for user in updated_users:
                print(f"ID: {user[0]}, 姓名: {user[1]}, 年龄: {user[2]}, 邮箱: {user[3]}")

        except sqlite3.Error as e:
            # 所有数据库异常统一捕获，回滚事务
            conn.rollback()
            print(f"\n❌ 操作失败：{e}")

if __name__ == "__main__":
    main()